﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: February 8, 2016
-- Significant mods by Jon Stocksdale, May 25, 2016
-- Description:	Receives the name of a defined view and potentially other commands and returns the result.
-- =============================================
CREATE PROCEDURE [dbo].[ReadDataFromDB_New] 
(
	-- Add the parameters for the stored procedure here
	@RequestorEntityID nvarchar(100), -- usually this will be USERID
	@CommandType nvarchar(50), -- View, Entity, Attributes, FunctionAttributes, RelatedEntities, NOSQL if RequestorID is 0, FunctionView
	@RequestedChain nvarchar(MAX) = NULL, -- ViewName, EntityName, or Chain to a particular relationship or Entity, or Function Name
	@ApprovedChain nvarchar(MAX) = NULL, -- A "chain-so-far" that has been verified/approved, if none is passed then use ; which means start from the beginning
	-- The following are the rights at the end of the approved chain.  
	@APPROVEDRD int = NULL,
	@APPROVEDRC int = null,
	@APPROVEDWD int = null,
	@APPROVEDWC int = null,
	-- The remaining parameters are optional
	@SD datetime = null,
	@ED datetime = null,
	@TD nvarchar(100) = '',
	@FunctionsOnly bit = 1, -- When getting related entities - get either ALL (0) or just those with Functions
	@JSONPARAMETERS nvarchar(MAX) = '', -- All other Parameters are specified as JSON: {"@P":"Val"}
	@Filter nvarchar(MAX) = null,
	@OrderBy nvarchar(MAX) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @EntityRelationshipList NVARCHAR(MAX),@AttributeList NVARCHAR(MAX)
	DECLARE @SQLQuery NVARCHAR(MAX), @STARTENTITY nvarchar(100)
	DECLARE @FNAttrList nvarchar(max), @ViewName nvarchar(max), @GROUPLIST nvarchar(max)

	IF @CommandType = 'LookupList' BEGIN
		DECLARE @ENTITYLIST nvarchar(1000)
		SET @ENTITYLIST = ''''+REPLACE(@RequestedChain,';',''',''') + ''''
		PRINT @ENTITYLIST
		EXEC LookupList @Filter=@Filter, @ENTITYLIST=@ENTITYLIST
	END ELSE
	IF @CommandType = 'Entity' BEGIN
		DECLARE @EntityTypeSubType nvarchar(100)
		SELECT @EntityTypeSubType=EntityType + COALESCE('.'+EntitySubType,'') FROM Entities WHERE EntityID=@RequestedChain
		SET @SQLQUERY = 'SELECT * FROM [{' + @EntityTypeSubType+'} WHERE EntityID="' + @RequestedChain + '"]'
		EXEC dbo.ExecuteRafikiNOSQL @NOSQL=@SQLQUERY
	END ELSE
	IF @CommandType = 'View' BEGIN
		SET @SQLQuery = dbo.ConvertRafikiNOSQL('SELECT * FROM {' + @RequestedChain + '}',@JSONPARAMETERS,@SD,@ED,@TD)
		IF NOT @Filter IS NULL BEGIN
			SET @Filter = REPLACE(@Filter,'"','''')
			SET @SQLQuery = @SQLQuery + ' WHERE ' + @Filter
		END
		IF NOT @OrderBy IS NULL
			SET @SQLQuery = @SQLQuery + ' ORDER BY ' + @OrderBy

		IF LEFT(@SQLQuery,6) = 'Error:' BEGIN
			SELECT @SQLQuery AS Error
			RETURN
		END

		EXECUTE (@SQLQuery)
	END ELSE IF @CommandType='RelatedEntities' BEGIN
		-- Move towards calling this as RelatedEntities - not Relationships. A call to relationships could mean something else
		-- Go Get List of Permitted Relationships with RequestorID, chain so far, chain approved so far
		-- Receive back approval flag and relationship list
		-- Pass that to new ViewRelated Entities stored procesure
		DECLARE @VerifiedPermissions TABLE (
			EntityID nvarchar(max),
			EntityType nvarchar(100),
			SRNameWithDir nvarchar(100),
			RD int,
			RC int,
			WD int,
			WC int)
		INSERT INTO @VerifiedPermissions
		EXEC VerifyRequestChain
			@RequestorEntityID = @RequestorEntityID,
			@RequestedChain = @RequestedChain,
			@ApprovedChain=@ApprovedChain,
			@ApprovedRD=@ApprovedRD,
			@ApprovedRC=@ApprovedRC,
			@ApprovedWD=@ApprovedWD,
			@ApprovedWC=@ApprovedWC
		declare @Entity1ID NVARCHAR(MAX), @Entity1Type NVARCHAR(100), @SRNameWithDir NVARCHAR(255)
		Declare @RD int, @RC int, @WD int, @WC int, @SelfFlag bit
		SELECT TOP 1 @Entity1ID=EntityID, @Entity1Type=EntityType, @SRNameWithDir=SRNameWithDir,
			@RD=RD, @RC=RC, @WD=WD, @WC=WC FROM @VerifiedPermissions
		IF CHARINDEX('Error: ',@Entity1ID) <> 0 BEGIN
			SELECT @Entity1ID AS Error
			RETURN 0
		END
		ELSE SET @Entity1ID=RTRIM(@Entity1ID)
		
		IF (@RequestorEntityID=@Entity1ID)
			SET @SelfFlag = 1
		ELSE SET @SelfFlag = 0
		EXEC [dbo].[ReadRelatedEntities]
		@Entity1ID = @Entity1ID,
		@Entity1Type = @Entity1Type,
		@SRNameWithDir = @SRNameWithDir,
		@ReadDirect = @RD,
		@ReadChild = @RC,
		@WriteDirect = @WD,
		@WriteChild = @WC,
		@SD=@SD,
		@ED=@ED,
		@TD=@TD,
		@FunctionsOnly=@FunctionsOnly,
		@SelfFunctions = @SelfFlag
	END ELSE IF @CommandType = 'Attributes' BEGIN
		SET @SQLQuery = 'SELECT * FROM {' + @RequestedChain + '}'
		EXECUTE GetAttributeNames @SQLQuery
	END ELSE IF @CommandType = 'FunctionAttributes' BEGIN
		SELECT @FNAttrList=DisplayAttributes, @ViewName=ViewName, @GROUPLIST=GroupableAttributes FROM dbo.DefinedRelationshipFunctions WHERE ShortFunctionName=@RequestedChain
		IF @FNAttrList IS NULL SET @FNAttrList='*'
		SET @SQLQuery = 'SELECT ' + @FNAttrList + ' FROM {' + @ViewName + '}'
		EXECUTE GetAttributeNames @SQLQuery, @GROUPLIST
	END ELSE IF @CommandType = 'FunctionView' BEGIN
		SELECT @FNAttrList=DisplayAttributes, @ViewName=ViewName FROM dbo.DefinedRelationshipFunctions WHERE ShortFunctionName=@RequestedChain
		IF @FNAttrList IS NULL 
			SET @FNAttrList='*'
			ELSE SET @FNAttrList=@FNAttrList + ',*'
		SET @SQLQuery = dbo.ConvertRafikiNOSQL('SELECT ' + @FNAttrList + ' FROM {' + @ViewName + '}',@JSONPARAMETERS,@SD,@ED,@TD)
		IF NOT @Filter IS NULL BEGIN
			SET @Filter = REPLACE(@Filter,'"','''')
			SET @SQLQuery = @SQLQuery + ' WHERE ' + @Filter
		END
		IF NOT @OrderBy IS NULL
			SET @SQLQuery = @SQLQuery + ' ORDER BY ' + @OrderBy

		IF LEFT(@SQLQuery,6) = 'Error:' BEGIN
			SELECT @SQLQuery AS Error
			RETURN
		END
		EXECUTE (@SQLQuery)
	END ELSE IF @CommandType = 'NOSQL' AND @RequestorEntityID = 0 BEGIN
		EXEC dbo.ExecuteRafikiNOSQL @NOSQL=@RequestedChain, @SD=@SD, @ED=@ED, @TD=@TD, @JSONPARAMETERS=@JSONPARAMETERS
	END ELSE
		SELECT 'The command given is invalid.' AS DataError
	RETURN 0


END